from django.shortcuts import render
import json
import random
from django.shortcuts import get_object_or_404, redirect
from django.http import JsonResponse, HttpResponse
from django.views import View
from django.db import connection
from django.utils import timezone
from django.contrib.auth.decorators import login_required
from django.contrib import messages
from django.core.paginator import Paginator

from .models import BlindBox, Product


def index(request):
    """首页视图"""
    # 获取推荐盲盒
    featured_boxes = BlindBox.objects.get_featured()
    
    # 获取热门超市
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT s.market_id, s.name, s.image_url, s.rating, s.address
            FROM tb_supermarket s
            ORDER BY s.rating DESC
            LIMIT 4
        """)
        popular_markets = dictfetchall(cursor)
    
    # 获取最新盲盒
    newest_boxes = BlindBox.objects.filter(status=1).order_by('-create_time')[:8]
    
    # 获取热销盲盒
    hot_boxes = BlindBox.objects.filter(status=1).order_by('-sales')[:8]

    context = {
        'featured_boxes': featured_boxes,
        'popular_markets': popular_markets,
        'newest_boxes': newest_boxes,
        'hot_boxes': hot_boxes,
    }
    return render(request, 'blindbox/index.html', context)

def box_list(request):
    """盲盒列表视图"""
    # 获取筛选参数
    category = request.GET.get('category')
    sort = request.GET.get('sort', 'default')
    market_id = request.GET.get('market_id')
    
    # 基础查询
    boxes = BlindBox.objects.filter(status=1)
    
    # 应用筛选
    if category:
        boxes = boxes.filter(category=category)
        
    if market_id:
        try:
            boxes = boxes.filter(market_id=int(market_id))
        except ValueError:
            pass
    
    # 应用排序
    if sort == 'price_asc':
        boxes = boxes.order_by('discount_price', 'original_price')
    elif sort == 'price_desc':
        boxes = boxes.order_by('-discount_price', '-original_price')
    elif sort == 'sales':
        boxes = boxes.order_by('-sales')
    elif sort == 'newest':
        boxes = boxes.order_by('-create_time')
    
    # 获取所有分类供筛选
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT DISTINCT category FROM tb_blind_box WHERE category IS NOT NULL
        """)
        categories = [row[0] for row in cursor.fetchall()]
    
    # 获取所有超市供筛选
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT market_id, name FROM tb_supermarket ORDER BY name
        """)
        markets = dictfetchall(cursor)
    
    # 分页
    paginator = Paginator(boxes, 12)  # 每页显示12个
    page_number = request.GET.get('page', 1)
    page_obj = paginator.get_page(page_number)
    
    context = {
        'boxes': page_obj,
        'categories': categories,
        'markets': markets,
        'selected_category': category,
        'selected_market': market_id,
        'selected_sort': sort,
    }
    return render(request, 'blindbox/list.html', context)


def box_detail(request, box_id):
    """盲盒详情视图"""
    box = get_object_or_404(BlindBox, box_id=box_id, status=1)
    
    # 获取盲盒内容(不直接展示，但用于动画)
    contents = box.get_contents()
    
    # 获取超市信息
    market = box.get_market()
    
    # 获取评价
    reviews = box.get_reviews(limit=5)
    
    # 获取推荐盲盒
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT b.* 
            FROM tb_blind_box b
            WHERE b.status = 1 
            AND b.category = %s
            AND b.box_id != %s
            ORDER BY b.sales DESC
            LIMIT 4
        """, [box.category, box.box_id])
        recommended_boxes = [
            {
                'box_id': row[0],
                'name': row[2],
                'original_price': row[4],
                'discount_price': row[5],
                'image_url': row[18]
            }
            for row in cursor.fetchall()
        ]
    
    context = {
        'box': box,
        'market': market,
        'reviews': reviews,
        'recommended_boxes': recommended_boxes,
        'content_count': len(contents)  # 只传递内容数量，不泄露具体内容
    }
    return render(request, 'blindbox/detail.html', context)


@login_required
def draw_box(request, box_id):
    """开盲盒动画和结果视图"""
    if request.method != 'POST':
        return redirect('blindbox:detail', box_id=box_id)
        
    box = get_object_or_404(BlindBox, box_id=box_id, status=1)
    
    # 检查库存
    if box.stock <= 0:
        messages.error(request, '很抱歉，该盲盒已售罄')
        return redirect('blindbox:detail', box_id=box_id)
    
    # 获取盲盒内容
    contents = box.get_contents()
    if not contents:
        messages.error(request, '该盲盒尚未配置内容')
        return redirect('blindbox:detail', box_id=box_id)
    
    # 随机选择一个内容
    drawn_product = random.choice(contents)
    
    # 记录抽盒记录
    user_id = request.user.user_id
    now = timezone.now()
    
    with connection.cursor() as cursor:
        try:
            # 检查是否存在用户购物车
            cursor.execute("""
                SELECT cart_id FROM tb_shopping_cart WHERE user_id = %s
            """, [user_id])
            
            cart_result = cursor.fetchone()
            if cart_result:
                cart_id = cart_result[0]
            else:
                # 创建购物车
                cursor.execute("""
                    INSERT INTO tb_shopping_cart (user_id, create_time, updated_at)
                    VALUES (%s, %s, %s)
                    RETURNING cart_id
                """, [user_id, now, now])
                cart_id = cursor.fetchone()[0]
            
            # 记录抽盒结果
            cursor.execute("""
                INSERT INTO draw_record (user_id, set_id, product_id, create_time)
                VALUES (%s, %s, %s, %s)
            """, [user_id, box.box_id, drawn_product['product_id'], now])
            
            # 添加到购物车
            cursor.execute("""
                INSERT INTO tb_cart_item (cart_id, box_id, quantity, added_at, selected)
                VALUES (%s, %s, %s, %s, %s)
            """, [cart_id, box.box_id, 1, now, True])
            
            # 更新盲盒库存和销量
            cursor.execute("""
                UPDATE tb_blind_box
                SET stock = stock - 1, sales = sales + 1
                WHERE box_id = %s
            """, [box.box_id])
            
            messages.success(request, f'恭喜您抽到了 {drawn_product["name"]}！已加入购物车')
            
        except Exception as e:
            messages.error(request, f'抽盒失败: {str(e)}')
            return redirect('blindbox:detail', box_id=box_id)
    
    context = {
        'box': box,
        'product': drawn_product,
    }
    return render(request, 'blindbox/draw_result.html', context)


def guide(request):
    """新手指南视图"""
    return render(request, 'blindbox/guide.html')


def about(request):
    """关于我们视图"""
    return render(request, 'blindbox/about.html')


def search(request):
    """搜索视图"""
    query = request.GET.get('q', '')
    if not query:
        return redirect('blindbox:list')
    
    # 使用原生SQL进行模糊搜索
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT * FROM tb_blind_box
            WHERE status = 1 AND (
                name LIKE %s OR
                description LIKE %s OR
                category LIKE %s
            )
            ORDER BY sales DESC
        """, [f'%{query}%', f'%{query}%', f'%{query}%'])
        results = dictfetchall(cursor)
    
    context = {
        'query': query,
        'results': results,
        'count': len(results)
    }
    return render(request, 'blindbox/search.html', context)


def product_preview(request, product_id):
    """商品预览API - 用于AJAX调用"""
    if request.headers.get('x-requested-with') != 'XMLHttpRequest':
        return HttpResponse(status=403)
        
    try:
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT * FROM tb_product WHERE product_id = %s
            """, [product_id])
            product = dictfetchone(cursor)
            
        if not product:
            return JsonResponse({'error': '商品不存在'}, status=404)
            
        return JsonResponse({
            'id': product['product_id'],
            'name': product['name'],
            'category': product['category'],
            'brand': product['brand'],
            'weight': product['weight'],
            'image_url': product['image_url'],
            'expiry_date': product['expiry_date'].strftime('%Y-%m-%d') if product['expiry_date'] else None,
            'original_price': float(product['original_price']) if product['original_price'] else None
        })
    except Exception as e:
        return JsonResponse({'error': str(e)}, status=500)


def agreement(request):
    """盲盒购买协议页面"""
    return render(request, 'blindbox/agreement.html')


# 辅助函数
def dictfetchall(cursor):
    """将游标中的结果转换为字典列表"""
    columns = [col[0] for col in cursor.description]
    return [dict(zip(columns, row)) for row in cursor.fetchall()]


def dictfetchone(cursor):
    """将游标中的一行结果转换为字典"""
    row = cursor.fetchone()
    if row:
        columns = [col[0] for col in cursor.description]
        return dict(zip(columns, row))
    return None

# 获取当前计数
# @app.route('/blindbox/get_sold_blind_box_count')
def get_sold_blind_box_count(request):
    try:
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT count FROM blindbox_stats WHERE id = 1
            """)
            product = dictfetchone(cursor)

        if not product:
            return JsonResponse({'error': '数据不存在'}, status=404)

        return JsonResponse({
            'count': product['count']
        })
    except Exception as e:
        return JsonResponse({'error': str(e)}, status=500)

